import openpyxl
import json
# import datetime
import os.path
import random
from datetime import  datetime
from xlrd import xldate_as_tuple
from decimal import Decimal

def readInfoData():
    wb = openpyxl.load_workbook('./sourceData/userinfo.xlsx')
    ws = wb.active
    baseData = {}
    for r in range(2, ws.max_row + 1):
        mobile = ""
        tmpDict = {}
        for c in range(1, ws.max_column + 1):
            tmpData = ws.cell(row=r, column=c).value
            if c == 1:
                tmpDict["id"] = tmpData
            elif c == 2:
                tmpDict["name"] = tmpData
                mobile = tmpData
            elif c == 3:
                tmpDict["certificate_no"] = tmpData
            elif c == 4:
                tmpDict["real_name"] = tmpData
        baseData[mobile] = tmpDict
    return baseData

def readOneData():
    # 打开excel文件,获取工作簿对象
    wb = openpyxl.load_workbook('./sourceData/userbankinfo.xlsx')
    # 从表单中获取单元格的内容
    ws = wb.active  # 当前活跃的表单
    # print(ws.max_column)
    # 将读取到的数据存入内存中（数据较小）
    baseData = {}
    for r in range(2, ws.max_row + 1):
        mobile = ""
        tmpDict = {}
        for c in range(1, ws.max_column + 1):
            # print("row is {}, column is {}, value is {}".format(r, c, ws.cell(row=r, column=c).value))
            tmpData = ws.cell(row=r, column=c).value
            if c == 1:
                tmpDict["address"] = tmpData
            elif c == 2:
                tmpDict["mobile"] = tmpData
                mobile = tmpData
            elif c == 3:
                tmpDict["name"] = tmpData
            elif c == 4:
                tmpDict["czjh"] = tmpData
            elif c == 5:
                tmpDict["bankCode"] = tmpData
            elif c == 6:
                tmpDict["bankName"] = tmpData
        # print("=======================行号：{}".format(r))
        baseData[mobile] = tmpDict
    return baseData

# print(json.dumps(readOneData()))


def readTwoItem(fileName): # baseinfo2.xlsx
    # 打开excel文件,获取工作簿对象
    wb = openpyxl.load_workbook(fileName, data_only=True)
    # 从表单中获取单元格的内容
    ws = wb.active  # 当前活跃的表单
    oldMobile = "-1"
    # 将读取到的数据存入内存中（数据较小）
    baseData = []
    for r in range(2, ws.max_row + 1):
        mobile = ""
        tmpDict = {}
        for c in range(1, ws.max_column + 1):
            tmpData = ws.cell(row=r, column=c).value
            # if c == 4:
                # tmpDict["address"] = tmpData
            if c == 1:
                tmpDict["mobile"] = tmpData
                mobile = tmpData
            elif c == 2:
                tmpDict["name"] = tmpData
            elif c == 3:
                tmpDict["czjh"] = tmpData
            elif c == 4:
                tmpDict["code"] = tmpData
            elif c == 5:
                date = datetime(*xldate_as_tuple(tmpData, 0))
                cell = date.strftime('%Y/%m/%d')  # ('%Y/%m/%d %H:%M:%S')
                print(cell)
                # tmpDict["start"] = tmpData.__str__()[0:10]#.__format__("%Y/%m/%d")
                tmpDict["start"] = cell
            elif c == 6:
                date = datetime(*xldate_as_tuple(tmpData, 0))
                cell = date.strftime('%Y/%m/%d')  # ('%Y/%m/%d %H:%M:%S')
                tmpDict["end"] = cell #tmpData.__str__()[0:10]
            elif c == 7:
                tmpDict["ll"] = tmpData
            elif c == 8:
                tmpDict["lx"] = tmpData
            elif c == 9:
                tmpDict["total"] = tmpData
        baseData.append(tmpDict)
        # print("line is {}".format(r))
    return baseData

# print(json.dumps(readTwoItem("baseinfo2.xlsx")))

xlData = readTwoItem("./sourceData/baseinfo.xlsx")
# print(json.dumps(xlData))
# print(xlData.__len__())
# print(json.dumps(xlData))

## 合并出借记录
def mergeBaseData(baseData):
    newData = []
    oldMobile = ""
    oldStr = ""
    oldDict = {}
    oldTotal = 0.00
    for i in range(0,len(baseData)):
        item = baseData.pop()
        curMobile = item["mobile"]

        _code = item["code"]
        _end = item["end"]
        _czjh = item["czjh"]
        _lx = item["lx"]
        _total = item["total"]
        # print(">>>>>>>>>{}>>>>>{}".format(_end, _total))

        if (curMobile != oldMobile and oldMobile != ""):
            # 号码不同
            # print("号码不相同")
            oldDict.setdefault("item", oldStr)
            oldDict.setdefault("newtotal", oldTotal)
            newData.append(oldDict)
            oldStr = ""
            oldTotal = 0.00
            oldDict = {}
        else:
            # 号码相同
            # print("号码相同")
            a = 1 + 1
        curStr = _code + "," + _end + "," + str(_czjh) + "," + str(Decimal(_lx).quantize(Decimal('0.00'))) + ";"
        oldStr += curStr
        # print("{}, {}".format(oldTotal, _total))
        oldTotal += _total
        oldDict = item
        oldMobile = curMobile

    if oldTotal > 0.00 :
        oldDict.setdefault("item", oldStr)
        oldDict.setdefault("newtotal", Decimal(oldTotal).quantize(Decimal('0.00')))
        newData.append(oldDict)
        oldStr = ""
        oldTotal = 0.00
        oldDict = {}
    return newData




print("===============")
# print(json.dumps(newlist))
# {"key":{"key1":"(签章)","key2":"甲方(签字)"}, --
# "partyAleader":"叶婷婷",  ----
# "UserIDNumber":"2576536515222277866",  ---
# "IdNumber":"'511322199001171984",   ---
# "contact":"18282459510",  --
# "address":"",  ---
# "partyBContact":"400-666-9068",  ---
# "day3":"8",  --
# "agreementInfo1":"AXT2019061701,2020年2月24日,1000,3.92;AXT2019080602,2020年2月25日,2000,7.23",  ---
# "agreementInfo2":"AXT2019091901,2020年3月3日,1000,1.61;AXT2020011603,2020年2月19日,1000,5.18;",  ---
# "acount1":"5017.94元",---
# "acount2":"5017.94元", ----
# "acculate":"（本金*项目年化预期收益）/365*应付利息天数",  ----
# "acountInfo":"",   ----
# "bank":"中国建设银行建设银行眉山彭山支行",  ---
# "bankInfo":"叶婷婷", --
# "bankNum":"6217003650002725144",
# "NumID":"2576536515222277866", ---
# "SignAName":"叶婷婷", --
# "SignBName":"中投摩根信息技术（北京）有限责任公司" -----
# }

# "signInfo": {
#   "userId": "433799218575052801",
#   "name": "中投摩根信息技术（北京）有限责任公司",
#   "organCode": "91110108306541619G",
#   "regType": "MERGE"
#  },

## grenter json
def generatorJSON():
    backResult = []
    baseList = mergeBaseData(xlData)
    userInfo = readOneData() # 用户基本信息
    ids = readInfoData()
    # print(json.dumps(ids))
    for index in range(0,len(baseList)):
        result = {}
        userinfo = {}
        keydict = {"key1": "(签章)", "key2": "甲方(签字)"}
        bizInfo = {"userId": "433799218575052801","name": "中投摩根信息技术（北京）有限责任公司","organCode": "91110108306541619G","regType": "MERGE"}
        result["signInfo"] = bizInfo
        result["key"] = keydict
        result["address"] = ""
        result["partyBContact"] = "400-666-9068"
        result["day3"] = str(datetime.now().day)
        result["acculate"] = "（本金*项目年化预期收益）/365*应付利息天数"
        result["acountInfo"] = ""
        result["SignBName"] = "中投摩根信息技术（北京）有限责任公司"
        item = baseList.pop()
        result["partyAleader"] = item["name"]
        customerId = ""
        aclID = ""
        for key in ids:
            # print("{}, {}".format(item["mobile"], str(key)))
            if str(item["mobile"]) == str(key):
                _dict = ids.get(key)
                aclID = _dict.get("certificate_no")
                customerId = _dict.get("id")
                break
            else:
                continue

        result["UserIDNumber"] = customerId
        result["NumID"] = customerId
        result["IdNumber"] = aclID
        result["contact"] = item["mobile"]
        result["agreementInfo1"] = item["item"]
        result["agreementInfo2"] = ""
        result["acount1"] =  str(item["newtotal"]) + "元"
        result["acount2"] = str(item["newtotal"]) + "元"
        # bankName = ""
        # bankCode = ""
        # for key in userInfo:
        #     # print("{},, {}".format(item["mobile"],str(key)))
        #     if item["mobile"] == key:
        #         tmpDict = userInfo.get(key)
        #         bankName = tmpDict.get("bankName")
        #         bankCode = tmpDict.get("bankCode")
        #         break
        #     else:
        #         continue
        result["bank"] = "海口联合农商银行"
        result["bankNum"] = "8981214000000019393"
        result["bankInfo"] = "中投摩根信息技术（北京）有限责任公司"
        result["SignAName"] = item["name"]
        backResult.append(result)
    return backResult

# 生成用户excel表
def write_excel():
    # file name =  P2P_date.xlsx
    _year = datetime.datetime.now().year
    _month = datetime.datetime.now().month
    _day = datetime.datetime.now().day
    tmpName = "P2P_{}{}{}_1.xlsx".format(_year, _month, _day)
    wb = openpyxl.Workbook()  # 创建工作簿
    ws1 = wb.create_sheet(index=0, title="清退用户信息") #默认插在最后
    # sheet1 = f.add_sheet(u'sheet1',cell_overwrite_ok=True) #创建sheet
    row0 = [u'姓名', u'手机号', u'身份证号', u'平台ID', u'出借信息', u'起息时间', u'截止时间',u'本金', u'利率', u'利息',u'合计本息', u'开户行', u'银行卡号']
    # 写表头（第一行）
    for i in range(len(row0)):
        # print(row0[i])
        ws1.cell(row=1, column=i + 1).value = row0[i]

    # 写具体的内容
    baseList = mergeBaseData(xlData)
    # print("baseList is {}".format(json.dumps(baseList)))
    # userInfo = readOneData() # 用户基本信息
    # print("userinfo is {}".format(json.dumps(userInfo)))
    ids = readInfoData()
    # print("ids is {}".format(json.dumps(ids)))
    for index in range(0,len(baseList)):
        item = baseList.pop()
        for i in range(len(row0)):
            ws1.cell(row=index + 2, column=1).value = str(item["name"])
            ws1.cell(row=index + 2, column=2).value = str(item["mobile"])
            customerId = ""
            aclID = ""
            for key in ids:
                if str(item["mobile"]) == str(key):
                    _dict = ids.get(key)
                    aclID = _dict.get("certificate_no")
                    customerId = _dict.get("id")
                    break
                else:
                    continue
            ws1.cell(row=index + 2, column=3).value = aclID
            ws1.cell(row=index + 2, column=4).value = customerId
            ws1.cell(row=index + 2, column=5).value =  str(item["item"])
            ws1.cell(row=index + 2, column=6).value =  str(item["start"])
            ws1.cell(row=index + 2, column=7).value =  str(item["end"])
            ws1.cell(row=index + 2, column=8).value =  str(item["czjh"])
            ws1.cell(row=index + 2, column=9).value =  str(item["ll"])
            ws1.cell(row=index + 2, column=10).value =  str(item["lx"])
            ws1.cell(row=index + 2, column=11).value =  str(item["newtotal"])
            bankName = ""
            bankCode = ""
            for key in userInfo:
                # print("{},, {}".format(item["mobile"],str(key)))
                if item["mobile"] == key:
                    tmpDict = userInfo.get(key)
                    bankName = tmpDict.get("bankName")
                    bankCode = tmpDict.get("bankCode")
                    break
                else:
                    continue
            ws1.cell(row=index + 2, column=12).value = bankName
            ws1.cell(row=index + 2, column=13).value = bankCode

    wb.save("./out/"+tmpName)



# 保存文件
def saveResultToFile():
    # file name =  P2P_count_date.txt
    dataDict = generatorJSON()
    _year = datetime.now().year
    _month = datetime.now().month
    _day = datetime.now().day
    tmpName = "P2P_{}_{}{}{}.txt".format(dataDict.__len__(), _year, _month, _day)
    fileName = "./out/" + tmpName
    if os.path.exists(fileName):
        print("file is exists")
        tmpName =  "P2P_{}_{}{}{}_{}.txt".format(dataDict.__len__(), _year, _month, _day, random.randint(0,9))
        fileName = "./out/" + tmpName
        print("filename is {}".format(fileName))
    else:
        print("file is not exists")

    f = open(fileName,'w', encoding='utf-8')
    f.write(json.dumps(dataDict))
    f.flush()
    f.close()

    return None

## 生成生成合同订单的json文件
saveResultToFile()
## 生成记录总的excel表
# write_excel()


